﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BusinessLogic;
using System.Web.Security;
using System.Data.SqlClient;
using System.Data;

namespace DataAccessLogic
{
    public static class ServiceReportDataAccess
    {
        public static ServiceReport GetByID(Guid serviceReportID)
        {
            ServiceReport sr = null;

            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@SERVICEREPORTID", serviceReportID));

            DataTable dt = DataAccess.Get(StoredProcedures.USP_SERVICEREPORT_GETBYID, parms);

            if(dt != null)
            {
                sr = new ServiceReport();                
                sr.ID = serviceReportID;
                
                DataRow row = dt.Rows[0];

                sr.AddedBy = DataAccess.CheckForStringColumn(row, "ADDEDBY");
                sr.ChangedBy = DataAccess.CheckForStringColumn(row, "CHANGEDBY");
                sr.AddedByID = DataAccess.CheckForGuidColumn(row, "ADDEDBYID");
                sr.ChangedByID = DataAccess.CheckForGuidColumn(row, "CHANGEDBYID");
                sr.Instrument = new Instrument() { ID = DataAccess.CheckForGuidColumn(row, "INSTRUMENTID") };
                sr.Instrument.ManufacturerType = new ManufacturerType() { ID = DataAccess.CheckForGuidColumn(row, "MANUFACTURERTYPECODEID"), Description = DataAccess.CheckForStringColumn(row, "MANUFACTURER") };
                sr.Instrument.InstrumentType = new InstrumentType() { ID = DataAccess.CheckForGuidColumn(row, "INSTRUMENTTYPECODEID"), Description = DataAccess.CheckForStringColumn(row, "INSTRUMENTTYPE") };
                sr.Instrument.Model = DataAccess.CheckForStringColumn(row, "MODEL");
                sr.Instrument.UnitNumber = DataAccess.CheckForStringColumn(row, "UNITNUMBER");
                sr.Instrument.SerialNumber = DataAccess.CheckForStringColumn(row, "SERIALNUMBER");
                sr.ServicePerformed = DataAccess.CheckForStringColumn(row, "SERVICEPERFORMED");
                sr.ServiceRequired = DataAccess.CheckForStringColumn(row, "SERVICEREQUIRED");
                sr.PONumber = DataAccess.CheckForStringColumn(row, "PONUMBER");
                sr.JobID = DataAccess.CheckForGuidColumn(row, "JOBID");
                sr.SentToIan = DataAccess.CheckForBooleanColumn(row, "SENTTOIAN");
                sr.SentToJenny = DataAccess.CheckForBooleanColumn(row, "SENTTOJENNY");
                
                sr.Parts = new PartList();
                sr.Parts.Fill(sr.ID);

                sr.Totals = new ServiceTotalList();
                sr.Totals.Fill(sr.ID);
            }

            return sr;
        }

        public static List<ServiceReport> GetAllSummariesByJobID(Guid jobID)
        {
            List<ServiceReport> reports = null;

            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@JOBID", jobID));

            DataTable dt = DataAccess.Get(StoredProcedures.USP_SERVICEREPORT_GETSUMMARIESBYJOBID, parms);

            if (dt != null)
            {
                reports = new List<ServiceReport>();

                foreach (DataRow row in dt.Rows)
                {
                    ServiceReport sr = new ServiceReport();

                    sr.ID = DataAccess.CheckForGuidColumn(row, "SERVICEREPORTID");
                    sr.AddedBy = DataAccess.CheckForStringColumn(row, "ADDEDBY");
                    sr.ChangedBy = DataAccess.CheckForStringColumn(row, "CHANGEDBY");
                    sr.AddedByID = DataAccess.CheckForGuidColumn(row, "ADDEDBYID");
                    sr.ChangedByID = DataAccess.CheckForGuidColumn(row, "CHANGEDBYID");
                    sr.Instrument = new Instrument() { ID = DataAccess.CheckForGuidColumn(row, "INSTRUMENTID") };
                    sr.Instrument.ManufacturerType = new ManufacturerType() { ID = DataAccess.CheckForGuidColumn(row, "MANUFACTURERTYPECODEID"), Description = DataAccess.CheckForStringColumn(row, "MANUFACTURER") };
                    sr.Instrument.InstrumentType = new InstrumentType() { ID = DataAccess.CheckForGuidColumn(row, "INSTRUMENTTYPECODEID"), Description = DataAccess.CheckForStringColumn(row, "INSTRUMENTTYPE") };
                    sr.Instrument.Model = DataAccess.CheckForStringColumn(row, "MODEL");
                    sr.Instrument.UnitNumber = DataAccess.CheckForStringColumn(row, "UNITNUMBER");
                    sr.Instrument.SerialNumber = DataAccess.CheckForStringColumn(row, "SERIALNUMBER");
                    sr.ServicePerformed = DataAccess.CheckForStringColumn(row, "SERVICEPERFORMED");
                    sr.ServiceRequired = DataAccess.CheckForStringColumn(row, "SERVICEREQUIRED");
                    sr.PONumber = DataAccess.CheckForStringColumn(row, "PONUMBER");
                    sr.SentToIan = DataAccess.CheckForBooleanColumn(row, "SENTTOIAN");
                    sr.SentToJenny = DataAccess.CheckForBooleanColumn(row, "SENTTOJENNY");

                    reports.Add(sr);
                }
            }

            return reports;
        }
                
        public static Guid Create(ServiceReport report)
        {
            //thoughts here are too put all of the logic possible in the stored procedure
            //pass all of the report properties to the stored procedure and then check for 
            //records that match the location, billto, instrument, and parts can be linked to it
            //otherwise create those records and use those ids

            Guid id = Guid.Empty;
            Guid providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());
            string outputParameterName = "@ID";

            if (report != null)
            {
                SqlParameter p = new SqlParameter();
                p.ParameterName = outputParameterName;

                if(report.ID != Guid.Empty)
                {
                    p.Value = report.ID;
                    id = report.ID;
                }
                else
                {
                    p.Value = Guid.Empty;
                }
                p.Direction = ParameterDirection.InputOutput;
                p.DbType = DbType.Guid;

                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(p);
                parms.Add(new SqlParameter("@INSTRUMENTSERIALNUMBER", report.Instrument.SerialNumber));
                parms.Add(new SqlParameter("@INSTRUMENTUNITNUMBER", report.Instrument.UnitNumber));
                parms.Add(new SqlParameter("@INSTRUMENTMODELNUMBER", report.Instrument.Model));
                parms.Add(new SqlParameter("@INSTRUMENTTYPEDESCRIPTION", report.Instrument.InstrumentType.Description));
                parms.Add(new SqlParameter("@INSTRUMENTMANUFACTURERDESCRIPTION", report.Instrument.ManufacturerType.Description));                
                parms.Add(new SqlParameter("@SERVICETOTALS", report.Totals.CreateXMLString()));
                parms.Add(new SqlParameter("@PARTS", report.Parts.CreateXMLString()));
                parms.Add(new SqlParameter("@PONUMBER", report.PONumber));
                parms.Add(new SqlParameter("@SERVICEPERFORMED", report.ServicePerformed));
                parms.Add(new SqlParameter("@SERVICEREQUIRED", report.ServiceRequired));
                parms.Add(new SqlParameter("@JOBID", report.JobID));
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));
                
                //WriteParametersToFile(parms);

                object obj = DataAccess.Create(StoredProcedures.USP_SERVICEREPORT_CREATE, outputParameterName, parms);

                if (obj != null)
                {
                    id = new Guid(obj.ToString());
                }
            }         

            return id;
        }

        public static void Delete(Guid serviceReportID)
        {
            DataAccess.Delete(StoredProcedures.USP_SERVICEREPORT_DELETE, serviceReportID);
        }

        public static void SendToJenny(Guid serviceReportID)
        {
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@SERVICEREPORTID", serviceReportID));

            DataAccess.Update(StoredProcedures.USP_SERVICEREPORT_SENDTOJENNY, parms);
        }

        public static void SendToIan(Guid serviceReportID)
        {
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@SERVICEREPORTID", serviceReportID));

            DataAccess.Update(StoredProcedures.USP_SERVICEREPORT_SENDTOIAN, parms);
        }

        public static void WriteParametersToFile(List<SqlParameter> parms)
        {
            System.IO.File.Delete(@"C:\parms.sql");
            System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\parms.sql");

            foreach (SqlParameter p in parms)
            {                
                string line = string.Format("declare {0} nvarchar(100) = '{1}';", p.ParameterName, p.Value);

                if(p.ParameterName.Contains("ID"))
                {
                    line = line.Replace("nvarchar(100)", "uniqueidentifier");
                }

                file.WriteLine(line);
            }

            file.Close();
        }
    }
}